﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;
using System.Collections;
using System.Web;

namespace DAL
{
    public class SanPhamQuery : DataProvide
    {
        public List<SanPhamDAL> LayDanhSachSanPham()
        {            
            List<SanPhamDAL> dsSanPham = new List<SanPhamDAL>();
            SqlConnection chuoiKetNoi = null;
            try
            {
                base.Connect();
                chuoiKetNoi = connection;
                string sqlString = String.Format(@"SELECT * FROM SANPHAM WHERE TinhTrang = '"+1+"'");

                SqlDataReader dr = ExecuteReader(sqlString);                
                while (dr.Read())
                {
                    SanPhamDAL sanPham = (SanPhamDAL)GetDataRowFromDataReader(dr);
                    dsSanPham.Add(sanPham);
                }
            }/*
            catch
            {
                return null;
            }*/
            finally
            {
                if (chuoiKetNoi != null && chuoiKetNoi.State == System.Data.ConnectionState.Open)
                    chuoiKetNoi.Close();
            }
            return dsSanPham;
        }

        public List<SanPhamDAL> LayDanhSachSanPhamKhuyenMai()
        {
            List<SanPhamDAL> dsSanPham = new List<SanPhamDAL>();
            SqlConnection chuoiKetNoi = null;
            try
            {
                base.Connect();
                chuoiKetNoi = connection;
                string sqlString = String.Format(@"SELECT * FROM SANPHAM WHERE PhanTramKM > " + 0);

                SqlDataReader dr = ExecuteReader(sqlString);
                while (dr.Read())
                {
                    SanPhamDAL sanPham = (SanPhamDAL)GetDataRowFromDataReader(dr);
                    dsSanPham.Add(sanPham);
                }
            }/*
            catch
            {
                return null;
            }*/
            finally
            {
                if (chuoiKetNoi != null && chuoiKetNoi.State == System.Data.ConnectionState.Open)
                    chuoiKetNoi.Close();
            }
            return dsSanPham;
        }

        public List<SanPhamDAL> LayDanhSachSanPhamMoi()
        {
            List<SanPhamDAL> dsSanPham = new List<SanPhamDAL>();
            SqlConnection chuoiKetNoi = null;
            try
            {
                base.Connect();
                chuoiKetNoi = connection;
                string sqlString = String.Format(@"SELECT * FROM SANPHAM WHERE SanPhamMoi = 'True'" );

                SqlDataReader dr = ExecuteReader(sqlString);
                while (dr.Read())
                {
                    SanPhamDAL sanPham = (SanPhamDAL)GetDataRowFromDataReader(dr);
                    dsSanPham.Add(sanPham);
                }
            }/*
            catch
            {
                return null;
            }*/
            finally
            {
                if (chuoiKetNoi != null && chuoiKetNoi.State == System.Data.ConnectionState.Open)
                    chuoiKetNoi.Close();
            }
            return dsSanPham;
        }

        /// <summary>
        /// Thêm một sản phẩm mới vào cơ sỡ dữ liệu
        /// </summary>
        /// <param name="sp"> sản phẩm được thêm</param>
        /// <returns>true nếu thêm thành công false nếu thêm thất bại</returns>
        public bool ThemSanPhamMoi(SanPhamDAL sp)
        {
            bool ketQua = true;
            try
            {
                base.Connect();

                string sqlString = "INSERT INTO SANPHAM(MaSanPham, TenSanPham, MaNhaSanXuat, MaLoaiSanPham, HinhAnh, GiaMua, GiaBan, ThongSoKyThuat, ThongTinMoTa, SoLuongTon, TinhTrang, SanPhamMoi, PhanTramKM) VALUES(N'" +
                    sp.MaSanPham + "', N'" +
                    sp.TenSanPham + "', N'" +
                    sp.MaNhaSanXuat + "', N'" +
                    sp.MaLoaiSanPham + "', '" +
                    sp.HinhAnh + "', " +
                    sp.GiaMua + ", " +
                    sp.GiaBan + ", N'" +
                    sp.ThongSoKyThuat + "', N'" +
                    sp.ThongTinMoTa + "'," +
                    sp.SoLuongTon + ", '" +
                    sp.TinhTrang + "', '" +
                    sp.SanPhamMoi + "', " +
                    sp.PhanTramKM + ")";

                ExecuteNonQuery(sqlString);
            }/*
            catch (Exception ex)
            {
                ketQua = false;
            }*/
            finally
            {
                if (connection != null && connection.State == System.Data.ConnectionState.Open)
                    Disconnect();
            }
            return ketQua;
        }

        /// <summary>
        /// Cập nhật sản phẩm
        /// </summary>
        /// <param name="sp"> sản phẩm được cập nhật</param>
        /// <returns>true nếu thêm thành công false nếu thêm thất bại</returns>
        public bool CapNhatSanPham(SanPhamDAL sanPham)
        {
            bool ketQua = true;
            try
            {
                base.Connect();

                string sqlString = "UPDATE SANPHAM" +
                    " SET TenSanPham = N'" + sanPham.TenSanPham + "', " +
                    " MaNhaSanXuat = N'" + sanPham.MaNhaSanXuat + "', " +
                    " MaLoaiSanPham = N'" + sanPham.MaLoaiSanPham + "', " +
                    " HinhAnh = '" + sanPham.HinhAnh + "', " +
                    " GiaMua = " + sanPham.GiaMua + ", " +
                    " GiaBan = " + sanPham.GiaBan + ", " +
                    " ThongSoKyThuat = N'" + sanPham.ThongSoKyThuat + "', " +
                    " ThongTinMoTa = N'" + sanPham.ThongTinMoTa + "', " +
                    " SoLuongTon = " + sanPham.SoLuongTon + ", " +
                    " TinhTrang = '" + sanPham.TinhTrang + "', " +
                    " SanPhamMoi = '" + sanPham.SanPhamMoi + "', " +
                    " PhanTramKM = " + sanPham.PhanTramKM  +
                    " WHERE MaSanPham = '" + sanPham.MaSanPham + "'";

                ExecuteNonQuery(sqlString);
            }/*
            catch (Exception ex)
            {
                ketQua = false;
            }*/
            finally
            {
                if (connection != null && connection.State == System.Data.ConnectionState.Open)
                    Disconnect();
            }
            return ketQua;
        }

        /// <summary>
        /// hiển thị sản phẩm theo mã loại
        /// </summary>
        /// <param name="dr">mã loại sản phẩm</param>
        /// <returns>danh sách sản phẩm theo loại</returns>
        public List<SanPhamDAL> LayDSSanPhamTheoMaLoai(string maloai)
        {
            List<SanPhamDAL> dsSanPham = new List<SanPhamDAL>();
            SqlConnection chuoiKetNoi = null;
            try
            {
                base.Connect();
                chuoiKetNoi = connection;
                string sqlString = String.Format(@"SELECT * FROM SANPHAM WHERE MaLoaiSanPham = '" +maloai+"' AND TinhTrang = 'True'");

                SqlDataReader dr = ExecuteReader(sqlString);
                while (dr.Read())
                {
                    SanPhamDAL sanPham = (SanPhamDAL)GetDataRowFromDataReader(dr);
                    dsSanPham.Add(sanPham);
                }
            }/*
            catch
            {
                return null;
            }*/
            finally
            {
                if (chuoiKetNoi != null && chuoiKetNoi.State == System.Data.ConnectionState.Open)
                    chuoiKetNoi.Close();
            }
            return dsSanPham;
        }

        /// <summary>
        /// Lấy sản phẩm theo mã sản phẩm
        /// </summary>
        /// <param name="dr">string masanpham</param>
        /// <returns>sanpham</returns>
        public SanPhamDAL LaySanPhamTheoMaSP(string masp)
        {
            SanPhamDAL sanPham = new SanPhamDAL();
            SqlConnection chuoiKetNoi = null;
            try
            {
                base.Connect();
                chuoiKetNoi = connection;
                string sqlString= String.Format(@"SELECT * FROM SANPHAM WHERE MaSanPham = '" + masp + "'");
                 
                SqlDataReader dr = ExecuteReader(sqlString);

                while (dr.Read())
                {
                    sanPham = (SanPhamDAL)GetDataRowFromDataReader(dr);
                }
            }/*
            catch
            {
                return null;
            }*/
            finally
            {
                if (chuoiKetNoi != null && chuoiKetNoi.State == System.Data.ConnectionState.Open)
                    chuoiKetNoi.Close();
            }
            return sanPham;
        }

        public bool CapNhatTinhTrangSanPham(SanPhamDAL sanPham)
        {
            bool ketQua = true;
            try
            {
                Connect();

                string chuoiLenh = "UPDATE SANPHAM" +
                    " SET TinhTrang = 'False'" +
                    " WHERE MaSanPham = '" + sanPham.MaSanPham +"'";

                ExecuteNonQuery(chuoiLenh);
            }
            catch (Exception ex)
            {
                ketQua = false;
            }
            finally
            {
                if (connection != null && connection.State == System.Data.ConnectionState.Open)
                    Disconnect();
            }
            return ketQua;
        }

        protected override object GetDataRowFromDataReader(SqlDataReader dr)
        {
            SanPhamDAL sanPham = new SanPhamDAL();
            sanPham.MaSanPham = dr.GetString(0);
            if (!dr.IsDBNull(1))
                sanPham.TenSanPham = dr.GetString(1);
            
            if (!dr.IsDBNull(2))
                sanPham.MaNhaSanXuat = dr.GetString(2);
            if (!dr.IsDBNull(3))
                sanPham.MaLoaiSanPham = dr.GetString(3);   
            if (!dr.IsDBNull(4))
            {
                //byte[] image = (byte[])dr.GetValue(2);
                sanPham.HinhAnh = dr.GetString(4);
                
                //stream.Write(image, 0, image.Length);
                //Bitmap bitmap = new Bitmap(stream);
                //Response.ContentType = "image/gif";
                //bitmap.Save(Response.OutputStream, ImageFormat.Gif);
            }
            
            if (!dr.IsDBNull(5))
                sanPham.GiaMua = float.Parse(dr.GetDouble(5).ToString());
            if (!dr.IsDBNull(6))
                sanPham.GiaBan = float.Parse(dr.GetDouble(6).ToString());
            if (!dr.IsDBNull(7))
                sanPham.ThongSoKyThuat = dr.GetString(7);
            if (!dr.IsDBNull(8))
                sanPham.ThongTinMoTa = dr.GetString(8);
            if (!dr.IsDBNull(9))
                sanPham.SoLuongTon = dr.GetInt32(9);
            if (!dr.IsDBNull(10))
                sanPham.TinhTrang = dr.GetBoolean(10);
            if (!dr.IsDBNull(11))
                sanPham.SanPhamMoi = dr.GetBoolean(11);
            if (!dr.IsDBNull(12))
                sanPham.PhanTramKM = float.Parse(dr.GetDouble(12).ToString());

            return (object)sanPham;
        }
    }
}
